'''
暂定在`records`表中存储。

数据段安排：

```json
// students: 学生信息
{
    "id": int (primary key),
    "stuId": str(255),
    "name": str(255),
    "ceilphone": str(255) | None,
    "ceilphone2": str(255) | None,
}
```

```json
// records: 手环上传信息
{
    "id": int (primary key),
    "stuId": str(255),
    "bandId": str(255),
    "stuName": str(255) | None,
    "timeIn": timestamp,
    "timeOut": timestamp | None,
    "calorie": double | None,
}
```
'''

import pymysql

from pymysql import cursors
from .config import PASSWORD

DATABASE_NAME = 'sport'

TABLE_NAME = 'records'
COL_NAMES = 'stuId, bandId, stuName, timeIn, timeOut, calorie'.split(', ')
COL_NAMES_SET = frozenset(COL_NAMES)

STU_TABLE_NAME = 'students'

TABLE_NAMES = {TABLE_NAME, STU_TABLE_NAME}

def getVisConn():
    '以visitor身份访问小程序自身的数据库'
    return pymysql.connect(
        host = 'localhost',
        user = 'visitor',
        password = '',
        database = 'demo_backend_spo',
        cursorclass = cursors.DictCursor,
        # autocommit = True,
    )

def getConn():
    return pymysql.connect(
        host = 'localhost',
        user = 'sport_root',
        charset = 'utf8mb4',
        password = PASSWORD,
        database = DATABASE_NAME,
        cursorclass = cursors.DictCursor,
        autocommit = True,
    )

def dbVisExec(command: str, *a):
    with getVisConn() as conn:
        with conn.cursor() as cursor:
            print('visitor db:', cursor.mogrify(command, a))
            cursor.execute(command, a)
            return cursor.fetchall()

def dbExec(command: str, *a):
    with getConn() as conn:
        with conn.cursor() as cursor:
            print(cursor.mogrify(command, a))
            cursor.execute(command, a)
            return cursor.fetchall()

def createDb():
    dbExec(
        f'CREATE TABLE IF NOT EXISTS `{TABLE_NAME}` ('
        '    `id` BIGINT NOT NULL AUTO_INCREMENT,'
        '    `stuId` VARCHAR(255) COLLATE UTF8_BIN NOT NULL,'
        '    `bandId` VARCHAR(255) COLLATE UTF8_BIN NOT NULL,'
        '    `stuName` VARCHAR(255) COLLATE UTF8_BIN,'
        '    `timeIn` DATETIME(0) NOT NULL,'
        '    `timeOut` DATETIME(0),'
        '    `calorie` DOUBLE,'
        '    PRIMARY KEY (`id`)'
        ') ENGINE=InnoDB DEFAULT '
        '    CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1'
    )
    dbExec(
        f'CREATE TABLE IF NOT EXISTS `{STU_TABLE_NAME}` ('
        '    `id` BIGINT NOT NULL AUTO_INCREMENT,'
        '    `stuId` VARCHAR(255) COLLATE UTF8_BIN NOT NULL,'
        '    `name` VARCHAR(255) COLLATE UTF8_BIN NOT NULL,'
        '    `ceilphone` VARCHAR(255) COLLATE UTF8_BIN,'
        '    `ceilphone2` VARCHAR(255) COLLATE UTF8_BIN,'
        '    `createTime` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP(0),'
        '    PRIMARY KEY (`id`)'
        ') ENGINE=InnoDB DEFAULT '
        '    CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1'
    )
